package cn.aishua.data;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import cn.aishua.ui.SplashActivity;

public class DatabaseOperator
{

    public static final String TBL_NAME_CITY = "city";
    public static final String TBL_NAME_FAVORITE = "my_favorite";
    public static final String TBL_INPUT_HISTORY = "input_history";

    private static DatabaseOperator operator;

    private DatabaseOperator()
    {

    }

    public static synchronized DatabaseOperator getInstance()
    {
        if( operator == null )
        {
            operator = new DatabaseOperator();

        }
        return operator;
    }

    public SQLiteDatabase openDb()
    {
        return SQLiteDatabase.openOrCreateDatabase( SplashActivity.dbFileName, null );
    }

    //-------------------------------- Table my_favorite start ------------------------------//

    public boolean addFavorite( int id, String vendorName, String averagePrice, String distance, String tags,
        String rebate, String picUrl )
    {
        Log.d( "DatabaseOperator", "addFavorite - start" );
        SQLiteDatabase db = openDb();
        Log.d( "DatabaseOperator", "db version: " + db.getVersion() );
        ContentValues values = new ContentValues();
        values.put( "id", id );
        values.put( "vendor_name", vendorName );
        values.put( "average_price", averagePrice );
        values.put( "distance", distance );
        values.put( "tags", tags );
        values.put( "rebate", rebate );
        values.put( "pic_url", picUrl );
        long rowId = db.insert( TBL_NAME_FAVORITE, null, values );
        db.close();
        Log.d( "DatabaseOperator", "row ID" + rowId );
        Log.d( "DatabaseOperator", "addFavorite - end" );
        return rowId != -1;
    }

    public boolean removeFavorite( int id )
    {
        Log.d( "DatabaseOperator", "removeFavorite - start" );

        SQLiteDatabase db = openDb();

        int affected = db.delete( TBL_NAME_FAVORITE, "id=" + id, null );
        Log.d( "DatabaseOperator", "affected count of row: " + affected );

        db.close();
        Log.d( "DatabaseOperator", "removeFavorite - end" );
        return affected > 0;
    }

    public boolean cleanFavorite()
    {
        Log.d( "DatabaseOperator", "cleanFavorite - start" );
        SQLiteDatabase db = openDb();

        int affected = db.delete( TBL_NAME_FAVORITE, "1", null );
        Log.d( "DatabaseOperator", "affected count of row: " + affected );
        db.close();
        Log.d( "DatabaseOperator", "cleanFavorite - end" );
        return affected > 0;
    }

    public boolean isMyFavorite( int id )
    {
        Log.d( "DatabaseOperator", "isMyFavorite - start" );
        boolean result = false;
        SQLiteDatabase db = openDb();
        Cursor cursor = db.query( true, TBL_NAME_FAVORITE, null, "id=" + id, null, null, null, null, null );
        if( cursor.getCount() > 0 )
        {
            result = true;
        }
        else
        {
            result = false;
        }
        Log.d( "DatabaseOperator", "result: " + result );
        cursor.close();
        db.close();
        Log.d( "DatabaseOperator", "isMyFavorite - end" );
        return result;
    }

    //-------------------------------- Table my_favorite end ------------------------------//

    //-------------------------------- Table city start ------------------------------//

    public List<String> getProvinceList()
    {
        List<String> provinceList = new ArrayList<String>();
        SQLiteDatabase db = openDb();
        Cursor cursor = db.query( true, TBL_NAME_CITY, new String[] { "province" }, null, null, null, null, "rowid",
            null );
        while( cursor.moveToNext() )
        {
            String province = cursor.getString( 0 );
            provinceList.add( province );
        }
        cursor.close();
        db.close();

        return provinceList;
    }

    public List<String> getCityList( String provinceName )
    {
        List<String> cityList = new ArrayList<String>();
        SQLiteDatabase db = openDb();
        Cursor cursor = db.query( true, TBL_NAME_CITY, new String[] { "city" }, "province=" + "\"" + provinceName
            + "\"", null, null, null, "rowid", null );
        while( cursor.moveToNext() )
        {
            String city = cursor.getString( 0 );
            cityList.add( city );
        }
        cursor.close();
        db.close();

        return cityList;
    }

    public List<String> getCountyList( String cityName )
    {
        List<String> countyList = new ArrayList<String>();
        SQLiteDatabase db = openDb();
        Cursor cursor = db.query( true, TBL_NAME_CITY, new String[] { "county" }, "city=" + "\"" + cityName + "\"",
            null, null, null, "rowid", null );
        while( cursor.moveToNext() )
        {
            String county = cursor.getString( 0 );
            countyList.add( county );
        }
        cursor.close();
        db.close();

        return countyList;
    }

    //-------------------------------- Table weathers city end ------------------------------//

    //-------------------------------- Table input_history start ------------------------------//

    public boolean insertHistory( String name )
    {
        Log.d( "DatabaseOperator", "insertHistory - start" );
        SQLiteDatabase db = openDb();

        ContentValues values = new ContentValues();
        values.put( "name", name );
        long rowId = db.insert( TBL_INPUT_HISTORY, null, values );
        db.close();
        Log.d( "DatabaseOperator", "row ID" + rowId );
        Log.d( "DatabaseOperator", "insertHistory - end" );
        return rowId != -1;
    }

    public List<String> getRecent5History()
    {

        List<String> nameList = new ArrayList<String>();
        SQLiteDatabase db = openDb();
        Cursor cursor = db.query( true, TBL_INPUT_HISTORY, new String[] { "name" }, null, null, null, null, "id desc",
            "5" );
        while( cursor.moveToNext() )
        {
            String name = cursor.getString( 0 );
            Log.d( "DatabaseOperator", "inserted name: " + name );
            nameList.add( name );
        }
        cursor.close();
        db.close();

        return nameList;

    }

}
